# coding: utf-8

import time
import requests
import pymysql
import re

from log import logger

url = 'http://i.sporttery.cn/odds_calculator/get_odds?i_format=json&poolcode[]=hhad&poolcode[]=had'
HEADER = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 '
    '(KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36'
}

def main():
    # 打开数据库连接
    logger.info('Mysql DB connect...')
    db = pymysql.connect("120.92.172.149", "root", "XdrgBnjI87", "lottery", use_unicode=True, charset="utf8")
    cursor = db.cursor()

    while True:
        try:
            logger.info('Get URL:%s' % url)
            r = requests.get(url, headers=HEADER)
            break
        except:
            time.sleep(5)
            pass

    datas = r.json()['data']
    for key in datas:
        logger.info('data:%s' % key)
        id = datas[key]['id']
        date = datas[key]['date']+' '+datas[key]['time']
        type = datas[key]['l_cn']
        home_name = datas[key]['h_cn']
        away_name = datas[key]['a_cn']
        home_id = datas[key]['h_id']
        away_id = datas[key]['a_id']

        if 'had' in datas[key]:
            rate_win = datas[key]['had']['h']
            rate_draw = datas[key]['had']['d']
            rate_lose = datas[key]['had']['a']
        else:
            rate_win = 0
            rate_draw = 0
            rate_lose = 0

        if 'hhad' in datas[key]:
            let_ball = datas[key]['hhad']['fixedodds']
            rate_win2 = datas[key]['hhad']['h']
            rate_draw2 = datas[key]['hhad']['d']
            rate_lose2 = datas[key]['hhad']['a']
        else:
            rate_win2 = 0
            rate_draw2 = 0
            rate_lose2 = 0

        home_rank = re.sub("\D", "", datas[key]['h_order'])
        away_rank = re.sub("\D", "", datas[key]['a_order'])

        inTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        # SQL 插入语句
        sql = """INSERT INTO tb_instant_match
              (`match_id`, `type`, `date`, `home_id`, `home_name`, `home_rank`, `away_id`, `away_name`, `away_rank`, `rate_win`, `rate_draw`, `rate_lose`, `let_ball`, `rate_win_2`, `rate_draw_2`, `rate_lose_2`, `create_time`)
              VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (id, type, date, home_id, home_name, home_rank, away_id, away_name, away_rank, rate_win, rate_draw, rate_lose, let_ball, rate_win2, rate_draw2, rate_lose2, inTime)

        sqlHis = """INSERT INTO tb_his_match
              (`match_id`, `type`, `date`, `home_id`, `home_name`, `home_rank`, `away_id`, `away_name`, `away_rank`, `rate_win`, `rate_draw`, `rate_lose`, `let_ball`, `rate_win_2`, `rate_draw_2`, `rate_lose_2`, `create_time`)
              VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (id, type, date, home_id, home_name, home_rank, away_id, away_name, away_rank, rate_win, rate_draw, rate_lose, let_ball, rate_win2, rate_draw2, rate_lose2, inTime)

        try:
            logger.info('execue SQL:%s' % sql)
            logger.info('execue SQL:%s' % sqlHis)
            # 执行sql语句
            cursor.execute(sql)
            cursor.execute(sqlHis)
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            logger.error(e)
            # 如果发生错误则回滚
            db.rollback()
    db.close()
    logger.info('end crawl')


if __name__ == '__main__':
    logger.info('start crawl')
    main()
